package com.gpscookie.vocabularystory;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.SocketTimeoutException;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.cookie.Cookie;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.params.BasicHttpParams;
import org.apache.http.params.HttpConnectionParams;
import org.apache.http.params.HttpParams;
import org.apache.http.util.EntityUtils;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class SimpleDBHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static String JSON = "json";
    private static String HTML = "html";
    private static final String DATABASE_NAME = "vocabularystory";
    private static final String CACHE_TABLE_NAME = "localcache";
    private static final String BOOK_TABLE_NAME = "book";
    private static final String ENTRY_TABLE_NAME = "entry";
    private static final String FIELD_SHORTDESC = "shortdesc";
    private static final String FIELD_DESC = "desc";
    private static final String FIELD_KEY = "key";
    private static final String FIELD_TITLE = "title";
    private static final String FIELD_ENTRY = "entry";
    private static final String FIELD_LEVEL = "level";
    private static final String FIELD_VOICE = "voice";    
    private static final String FIELD_TOTAL = "total";
    private static final String FIELD_CURRENT = "current";
    private static final String FIELD_URL = "url";
    private static final String FIELD_TIMESTAMP = "timestamp";
    private static final String FIELD_PAYLOAD = "payload";
    private static final String FIELD_FORMAT = "format";
    private static final String CACHE_TABLE_CREATE =
                "CREATE TABLE IF NOT EXISTS "+CACHE_TABLE_NAME+
                "("+FIELD_URL+" TEXT PRIMARY KEY ASC, "+
                FIELD_TIMESTAMP+" TEXT DEFAULT CURRENT_TIMESTAMP, "+
                FIELD_PAYLOAD+" TEXT, "+FIELD_FORMAT+" TEXT);";
    private static final String BOOK_TABLE_CREATE = 
    	"CREATE TABLE IF NOT EXISTS "+BOOK_TABLE_NAME+
    	"("+FIELD_KEY+" TEXT PRIMARY KEY ASC, "+
    	FIELD_TITLE+" TEXT, "+
    	FIELD_ENTRY+" TEXT, "+
    	FIELD_URL+" TEXT, "+
    	FIELD_TIMESTAMP+" TEXT DEFAULT CURRENT_TIMESTAMP, "+
    	FIELD_TOTAL+" INT, "+
    	FIELD_CURRENT+" INT);";
    private static final String ENTRY_TABLE_CREATE = 
    	"CREATE TABLE IF NOT EXISTS "+ENTRY_TABLE_NAME+
    	"("+FIELD_KEY+" TEXT, "+
    	FIELD_LEVEL+" INT DEFAULT 0, "+
    	FIELD_ENTRY+" TEXT, "+
    	FIELD_TIMESTAMP+" TEXT DEFAULT CURRENT_TIMESTAMP,"+
    	FIELD_SHORTDESC+" TEXT, "+
    	FIELD_DESC+" TEXT, "+
    	FIELD_URL+" TEXT, "+
    	FIELD_VOICE+" TEXT, PRIMARY KEY ("+FIELD_KEY+","+FIELD_ENTRY+"));";
    
	private Context mContext;
	
    SimpleDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CACHE_TABLE_CREATE);
        db.execSQL(BOOK_TABLE_CREATE);
        db.execSQL(ENTRY_TABLE_CREATE);
    }

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}
	
	protected Cursor queryCacheRecord(SQLiteDatabase db, String pk) {		
		Cursor record = db.query(CACHE_TABLE_NAME, new String[]{FIELD_PAYLOAD, FIELD_TIMESTAMP}, FIELD_URL+"=?", new String[]{pk}, null, null, FIELD_TIMESTAMP+" DESC");
		return record;
	}
	
	public String getHTML(String url, boolean byforce) throws IOException {
		String rtn = "";
		if (byforce) {
			rtn = getURL(url, true, HTML);
		} else {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor record = queryCacheRecord(db, url);		
			if (record.moveToFirst()) {
				// if found, check the cache life
				Log.i("SimpleDBHelper.getHTML():...cache...", ":"+Utils.INSTANCE.cache_life);
				Log.i("SimpleDBHelper.getHTML():...record time...", record.getString(1));
				String ts = record.getString(1);
				if (Utils.INSTANCE.howLongTillNow(ts)>=Utils.INSTANCE.cache_life) {
					Log.i("SimpleDBHelper.getHTML():...expired...", "...force sync...");
					record.close();
					rtn = getURL(url, true, HTML);
				} else {
					rtn = record.getString(0);
					record.close();
				}				
			} else {
				// not found
				record.close();
				rtn = getURL(url, true, HTML);				
			}	
			db.close();
		}
		return rtn;		
	}
	
	public int countEntryList(String key, int filter) {
		Log.i("SimpleDBHelper:countEntryList():", String.format("key:%s, filter:%d", key, filter));
		ArrayList<Entry> rtn = new ArrayList<Entry>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor record = null;
		String sql = String.format("SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s=? AND %s>=? ORDER BY %s ASC", 
				FIELD_KEY, 
				FIELD_ENTRY, 
				FIELD_URL, 
				FIELD_VOICE, 
				FIELD_SHORTDESC, 
				FIELD_DESC, 
				FIELD_LEVEL,
				ENTRY_TABLE_NAME,
				FIELD_KEY,
				FIELD_LEVEL,
				FIELD_ENTRY);
		Log.i("SimpleDBHelper:countEntryList():", sql);
		record = db.rawQuery(sql, new String[]{key, String.valueOf(filter)});
		if (record==null) {
			db.close();
			return -1;
		} else {
			int rtnCount = record.getCount();
			record.close();
			db.close();
			return rtnCount;
		}
	}
	
	public Entry queryEntry(String key, int filter, int pos) {
		Log.i("SimpleDBHelper:queryEntry():", String.format("key:%s, pos:%d", key, pos));
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor record = null;
		String sql = String.format("SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s=? AND %s>=? ORDER BY %s ASC LIMIT 1 OFFSET %d", 
				FIELD_KEY, 
				FIELD_ENTRY, 
				FIELD_URL, 
				FIELD_VOICE, 
				FIELD_SHORTDESC, 
				FIELD_DESC, 
				FIELD_LEVEL,
				ENTRY_TABLE_NAME,
				FIELD_KEY,
				FIELD_LEVEL,
				FIELD_ENTRY,
				pos);
		Log.i("SimpleDBHelper:queryEntry():", sql);
		record = db.rawQuery(sql, new String[]{key, String.valueOf(filter)});
		Entry obj = new Entry();
		if (record!=null && record.moveToNext()) {				
				obj.setKey(record.getString(0));
				obj.setEntry(record.getString(1));
				obj.setUrl(record.getString(2));
				obj.setVoice(record.getString(3));
				obj.setShortdesc(record.getString(4));
				obj.setDesc(record.getString(5));
				obj.setLevel(record.getInt(6));				
		} else {
			obj = null;
		}
		record.close();
		db.close();
		return obj;
	}
	
	public ArrayList<Entry> queryEntryList(String key, int currentPage, int size, int filter) {
		int offset = (currentPage-1)*Utils.INSTANCE.pageSize;
		Log.i("SimpleDBHelper:queryEntryList():", String.format("key:%s, size:%d, offset:%d", key, size, offset));
		ArrayList<Entry> rtn = new ArrayList<Entry>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor record = null;
		String sql = String.format("SELECT %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s=? AND %s>=? ORDER BY %s ASC LIMIT %d OFFSET %d", 
				FIELD_KEY, 
				FIELD_ENTRY, 
				FIELD_URL, 
				FIELD_VOICE, 
				FIELD_SHORTDESC, 
				FIELD_DESC, 
				FIELD_LEVEL,
				ENTRY_TABLE_NAME,
				FIELD_KEY,
				FIELD_LEVEL,
				FIELD_ENTRY,
				size,
				offset);
		Log.i("SimpleDBHelper:queryEntryList():", sql);
		record = db.rawQuery(sql, new String[]{key, String.valueOf(filter)});
		while (record!=null && record.moveToNext()) {
				Entry obj = new Entry();
				obj.setKey(record.getString(0));
				obj.setEntry(record.getString(1));
				obj.setUrl(record.getString(2));
				obj.setVoice(record.getString(3));
				obj.setShortdesc(record.getString(4));
				obj.setDesc(record.getString(5));
				obj.setLevel(record.getInt(6));
				rtn.add(obj);
		}
		record.close();
		db.close();
		Log.i("SimpleDBHelper:queryEntryList():", String.format("items size:%d", rtn.size()));
		return rtn;		
	}
	
	public Entry queryEntry(String key, String entry) {
		Entry obj = new Entry();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor record = db.query(ENTRY_TABLE_NAME, 
				new String[]{FIELD_KEY, FIELD_ENTRY, FIELD_URL, FIELD_VOICE, FIELD_SHORTDESC, FIELD_DESC, FIELD_LEVEL}, 
				FIELD_KEY+"=? and "+FIELD_ENTRY+"=?", new String[]{key, entry}, null, null, FIELD_TIMESTAMP+" DESC");
		if (record!=null && record.moveToFirst()) {
			obj.setKey(record.getString(0));
			obj.setEntry(record.getString(1));
			obj.setUrl(record.getString(2));
			obj.setVoice(record.getString(3));
			obj.setShortdesc(record.getString(4));
			obj.setDesc(record.getString(5));
			obj.setLevel(record.getInt(6));
		} else {
			obj = null;
		}
		record.close();
		db.close();
		return obj;
	}
	
	public void queryMyBooks(ArrayList<Book> booklist) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor record = db.query(BOOK_TABLE_NAME, new String[]{FIELD_KEY, FIELD_TITLE, FIELD_URL, FIELD_ENTRY, FIELD_TOTAL, FIELD_CURRENT}, null, null, null, null, FIELD_TIMESTAMP+" DESC");
		while(record!=null && record.moveToNext()) {
			Cursor record2 = db.rawQuery("SELECT count(entry) FROM entry WHERE key=?", 
					new String[]{record.getString(0)});			
			Book book = new Book();
			book.setKey(record.getString(0));
			book.setTitle(record.getString(1));
			book.setUrl(record.getString(2));
			book.setEntry(record.getString(3));
			book.setTotal(record.getInt(4));
			if (record2!=null && record2.moveToFirst()) {
				book.setCurrent(record2.getInt(0));
			}
			record2.close();
			booklist.add(book);
			Log.i("SimpleDBHelper:queryMyBooks(): find ", book.getTitle()+" "+book.getCurrent());
		}
		record.close();
		db.close();
	}
	
	public void deleteBook(String book) {
		if (book!=null) {
			SQLiteDatabase db = this.getReadableDatabase();
			db.delete(BOOK_TABLE_NAME, FIELD_KEY+"=?", new String[]{book});
			db.delete(ENTRY_TABLE_NAME, FIELD_KEY+"=?", new String[]{book});
			db.close();		
			Log.d("SimpleDBHelper:deleteBook:", book);
		}
	}
	
	public Book queryBook(String key) {
		Book book = new Book();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor record = db.query(BOOK_TABLE_NAME, new String[]{FIELD_KEY, FIELD_TITLE, FIELD_URL, FIELD_ENTRY, FIELD_TOTAL, FIELD_CURRENT}, FIELD_KEY+"=?", new String[]{key}, null, null, FIELD_TIMESTAMP+" DESC");
		if (record!=null && record.moveToFirst()) {
			book.setKey(record.getString(0));
			book.setTitle(record.getString(1));
			book.setUrl(record.getString(2));
			book.setEntry(record.getString(3));
			book.setTotal(record.getInt(4));
			book.setCurrent(record.getInt(5));
		} else {
			book = null;
		}
		record.close();
		db.close();
		return book;		
	}
	
	public boolean saveEntry(Entry entry) {
		try {
			Log.i("SimpleDBHelper:saveEntry():", entry.getKey()+" "+entry.getEntry());
        	ContentValues values = new ContentValues();
        	values.put(FIELD_URL, entry.getUrl());
        	values.put(FIELD_ENTRY, entry.getEntry());
        	values.put(FIELD_KEY, entry.getKey());
        	values.put(FIELD_SHORTDESC, entry.getShortdesc());
        	values.put(FIELD_DESC, entry.getDesc());
        	if (entry.getLevel()>=0) {
            	values.put(FIELD_LEVEL, entry.getLevel());
        	}
        	values.put(FIELD_VOICE, entry.getVoice());
        	Date now = new Date();
        	values.put(FIELD_TIMESTAMP, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now));
        	SQLiteDatabase db = this.getWritableDatabase();
        	if (db.update(ENTRY_TABLE_NAME, values, FIELD_KEY+"=? and "+FIELD_ENTRY+"=?", new String[]{entry.getKey(), entry.getEntry()})==0) {
        		Log.i("SimpleDBHelper:saveEntry(): new entry", entry.getEntry());
        		db.replace(ENTRY_TABLE_NAME, null, values);        		
        	}
        	db.close();			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}		
	}
	
	public boolean saveBook(Book book) {
		try {
			Log.i("SimpleDBHelper:saveBook():", book.getKey()+" "+book.toString());
        	ContentValues values = new ContentValues();
        	values.put(FIELD_URL, book.getUrl());
        	values.put(FIELD_TITLE, book.getTitle());
        	values.put(FIELD_KEY, book.getKey());
        	values.put(FIELD_ENTRY, book.getEntry());
        	values.put(FIELD_TOTAL, book.getTotal());
        	values.put(FIELD_CURRENT, book.getCurrent());
        	Date now = new Date();
        	values.put(FIELD_TIMESTAMP, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now));
        	SQLiteDatabase db = this.getWritableDatabase();
        	if (db.replace(BOOK_TABLE_NAME, null, values)<0) {
        		Log.i("SimpleDBHelper:saveBook(): already saved...", book.getTitle());
        		db.update(BOOK_TABLE_NAME, values, FIELD_KEY+"=?", new String[]{book.getKey()});
        	}
        	db.close();			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public JSONObject getJSON(String url, boolean byforce) {
		String json = "";
		SQLiteDatabase db = null;
		try {			
			if (byforce) {
				Log.i("SimpleDBHelper.getJSON():", "...byforce...");
				json = getURL(url, true, JSON);
			} else {
				db = this.getReadableDatabase();
				Cursor record = queryCacheRecord(db, url);		
				if (record.moveToFirst()) {
					// if found, check the cache life
					Log.i("SimpleDBHelper.getJSON():...cache life...", ":"+Utils.INSTANCE.cache_life);
					Log.i("SimpleDBHelper.getJSON():...record time...", record.getString(1));
					String ts = record.getString(1);
					if (Utils.INSTANCE.howLongTillNow(ts)>=Utils.INSTANCE.cache_life && Utils.INSTANCE.cache_life>=0) {
						Log.i("SimpleDBHelper.getJSON():...expired...", "...force sync...");
						record.close();
						json = getURL(url, true, JSON);
					} else {
						Log.i("SimpleDBHelper.getJSON():...NOT expired...", "...local cache...");
						json = record.getString(0);
						record.close();
						try {
							JSONObject tmp = new JSONObject(json);
						} catch (JSONException e) {
							// the saved cache is not a json, reload it
							Log.i("SimpleDBHelper.getJSON():", "...INVALID local cache...");
							json = getURL(url, true, JSON);
						}
					}				
				} else {
					// not found
					record.close();
					Log.i("SimpleDBHelper.getJSON():", "...NOT FOUND local cache...");
					json = getURL(url, true, JSON);				
				}
				
			}
		} catch (Exception e) {
			Log.e("SimpleDBHelper:getJSON():", e.toString());
			json = String.format(Utils.JSONERROR, -1, mContext.getString(R.string.networkerror));
		} finally {
			if (db!=null) {
				db.close();
			}
		}
		JSONObject obj = null;
		try {
			obj = new JSONObject(Utils.JSONNULL);
			if (json!=null) {
				obj = new JSONObject(json);
			}
		} catch (JSONException e) {
			Log.e("SimpleDBHelper:getJSON():", e.toString());
		}
		return obj;
	}
	
	private static InputStream downloadImage(Context context, String src) {
		URL url;
		try {
			try {
				url = new URL(src);
			} catch (MalformedURLException e) {
				// TODO: append site prefix
				url = new URL("http://..."+src);
			}
			InputStream is = (InputStream) url.getContent();
			return is;
		} catch (Exception e) {
			return null;
		}		
	}
	
	public File getRandomOutputFile() {
		boolean mExternalStorageAvailable = false;
		boolean mExternalStorageWriteable = false;
		String state = Environment.getExternalStorageState();

		if (Environment.MEDIA_MOUNTED.equals(state)) {
		    // We can read and write the media
		    mExternalStorageAvailable = mExternalStorageWriteable = true;
		} else if (Environment.MEDIA_MOUNTED_READ_ONLY.equals(state)) {
		    // We can only read the media
		    mExternalStorageAvailable = true;
		    mExternalStorageWriteable = false;
		} else {
		    // Something else is wrong. It may be one of many other states, but all we need
		    //  to know is we can neither read nor write
		    mExternalStorageAvailable = mExternalStorageWriteable = false;
		}
		
		if (mExternalStorageAvailable && mExternalStorageWriteable) {
			File f = new File(Environment.getExternalStorageState()+"/Android/data/com.linkadvantage/files/"+Calendar.getInstance().getTimeInMillis());
			return f;
		} else {
			File f = new File(mContext.getCacheDir()+"/"+Calendar.getInstance().getTimeInMillis());
			return f;
		}
	}
	
	/**
	 * Get contents of a url from the web, save it at loacl cache if asked
	 * @param url
	 * @throws SocketTimeoutException 
	 */
	protected String getURL(String url, boolean save, String format) throws IOException {
		// sync. local DB with Web
		Log.i("SimpleDBHelper.getURL():", url);
		String rtn;
		
		DefaultHttpClient br = new DefaultHttpClient();
	    HttpParams params = new BasicHttpParams();

	    // Turn off stale checking.  Our connections break all the time anyway,
	    // and it's not worth it to pay the penalty of checking every time.
	    HttpConnectionParams.setStaleCheckingEnabled(params, false);

	    // Default connection and socket timeout of 30 seconds.  Tweak to taste.
	    HttpConnectionParams.setConnectionTimeout(params, Utils.INSTANCE.timeout * 1000);
	    HttpConnectionParams.setSoTimeout(params, Utils.INSTANCE.timeout * 1000);
	    HttpConnectionParams.setSocketBufferSize(params, 8192);
	    
	    br.setParams(params);
		
    	HttpGet request = new HttpGet(url);
    	
    	try {
    		if (Utils.INSTANCE.Cookie != null) {
    			List<Cookie> cookies = Utils.INSTANCE.Cookie.getCookies();
	    		for (int i=0; i<cookies.size(); i++) {
	    			br.getCookieStore().addCookie(cookies.get(i));
	    		}
    		}
    		HttpResponse response = br.execute(request);	    		
    		HttpEntity resEntity = response.getEntity();	    		
            if (resEntity != null) {            	
            	rtn = EntityUtils.toString(resEntity, "utf8");
            	Log.i("SimpleDBHelper.getURL():", rtn);
            	if (save) {
	            	ContentValues values = new ContentValues();
	            	values.put(FIELD_URL, url);
	            	values.put(FIELD_PAYLOAD, rtn);
	            	values.put(FIELD_FORMAT, format);
	            	Date now = new Date();
	            	values.put(FIELD_TIMESTAMP, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now));
	            	SQLiteDatabase db = this.getWritableDatabase();
	            	if (db.replace(CACHE_TABLE_NAME, null, values)<0) {
	            		db.update(CACHE_TABLE_NAME, values, FIELD_URL+"=?", new String[]{url});
	            	}
	            	db.close();
            	}
            	return rtn;
            } else {
            	return null;
            }
		} catch (IOException e) {
			if (url.contains(".appspot.com")) {
				Utils.INSTANCE.chooseserver = 1;
			} else if (url.contains("delicaterock.com")) {
				Utils.INSTANCE.chooseserver = 2;
			} else if (url.contains("gpscookie.cn")) {
				Utils.INSTANCE.chooseserver = 0;
			} else {
				Utils.INSTANCE.chooseserver = 0;
			}
			throw e;
		} catch (Exception e) {
			e.printStackTrace();
			Log.e("SimpleDBHelper.getURL():", e.toString());
			return null;
		}
	}	
}
